package com.situ.achievementsystem.employee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.achievementsystem.employee.model.EmployeeModel;
import com.situ.achievementsystem.util.JDBCUtil;


public class EmployeeDao {

	private Connection con = null;
	private PreparedStatement ps = null;
	
	public String insert(EmployeeModel model) {
		String sql = "insert into employee(code,name,pass,code_dept)values(?,?,?,?)";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getCode());
			ps.setString(2,model.getName());
			ps.setString(3,model.getPass());
			ps.setString(4,model.getCodeDept());
			int res = ps.executeUpdate();
//			System.out.println(res);
			return res + "";
//			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
//	public String insert(EmployeeModel model) {
//		String sql = "insert into person(code,name,pass)values(?,?,?)";
//		return null;
//	}
	
	public String delete(EmployeeModel model) {
		String sql = "delete from employee where code = ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getCode());
			int res = ps.executeUpdate();
			System.out.println(res);
			return res+"";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public String update(EmployeeModel model) {
		String sql = "update employee set name =?,pass=? where code=?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getName());
			ps.setString(2,model.getPass());
			ps.setString(3,model.getCode());
			int res = ps.executeUpdate();
			System.out.println(res);
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
	
	public List<EmployeeModel> select(EmployeeModel model){
		StringBuilder sql = new StringBuilder("select code,name,pass,code_dept,(select name from department where code=code_dept)nameDept from employee where 1=1");
		List<Object> list = appendWhere(sql,model);
		ResultSet rs = null;
		List<EmployeeModel> result = new ArrayList<>();
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql.toString());
			for(int i=0;i<list.size();i++) {
				ps.setObject(i+1,list.get(i));
			}
			rs = ps.executeQuery();
			while(rs.next()) {
				String code = rs.getString("code");
				String codeDept = rs.getString("code_dept");
				String nameDept = rs.getString("nameDept");
				String pass = rs.getString("pass");
				String name = rs.getString("name");
				result.add(new EmployeeModel(code,name,pass,codeDept,nameDept));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	private List<Object> appendWhere(StringBuilder sql, EmployeeModel model) {
		List<Object> list = new ArrayList<>(); 
		String code = model.getCode();
		if(code!=null && !code.trim().isEmpty()) {
			sql.append(" and code like ? ");
			list.add(code);
		}
		String name = model.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name like ? ");
			list.add(name);
		}
		String pass = model.getPass();
		if(pass!=null && !pass.trim().isEmpty()) {
			sql.append(" and pass like ? ");
			list.add(pass);
		}
		return list;
	}

	public String updatepass(EmployeeModel model) {
		String sql = "update employee set pass =? where code=?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getPass());
			ps.setString(2,model.getCode());
			int res = ps.executeUpdate();
			System.out.println(res);
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}

	public String updateDept(EmployeeModel model) {
		String sql = "update employee set code_dept=? where code=?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1,model.getCodeDept());
			ps.setString(2,model.getCode());
			int res = ps.executeUpdate();
			System.out.println(res);
			return res + "";
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(con,ps);
		}
		return null;
	}
}
